SQLite Triggers is a sql series of sql statements that are executed automatically to perform/invoked when a specified database event occurs, such as creation or updating record or deletion of record/table.

Each trigger will have a unique  name to the database. If we dropped the table the associated triggers also be deleted or we can drop the trigger with our self using ‘DROP TRIGGER’ statement.

If we create a trigger then it can’t be modified, or if we want changes into our created trigger then at first drop the trigger and then recreate it.

Here we specifying some important points about SQLite triggers:

·   A trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a particular database table occurs, or whenever an UPDATE of one or more specified columns of a table are updated. 

·  SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional. 

·   Both the WHEN clause and the trigger actions can access elements of the row being inserted, deleted or updated using references of the formNEW.column-name and OLD.column-name, where column-name is the name of a column from the table that the trigger is associated with. 

·   If a WHEN clause is supplied, the SQL statements specified are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the SQL statements are executed for all rows.

·   The BEFORE or AFTER keyword determines when the trigger actions will be executed relative to the insertion, modification or removal of the associated row. 

·    If the table is dropped then the Triggers are automatically dropped

·   The table to be modified must exist in the same database as the table or view to which the trigger is attached and one must use justtablename not database.tablename.

·   To raise an exception within a trigger-program an special SQL function RAISE() is available. 

·    The syntax to create a trigger on a table is as follows:

CREATE [TEMP | TEMPORARY] TRIGGER trigger-name

[BEFORE | AFTER] database-event ON [database-name .]table-name trigger-action. 

·   The trigger-name is user-specified and must be unique across all objects in the database—it cannot share the same name as a table, view, or index.

·   The trigger-action is further defined as

[FOR EACH ROW | FOR EACH STATEMENT] [WHEN expression]

BEGIN

trigger-step; [trigger-step;] *

END

·    As you might expect, the syntax to drop a trigger is simply

DROP TRIGGER [database-name .] table-name 

·   If you forget the name of a trigger, you can query sqlite_master using type = 'trigger' to find all the triggers on the current database. 

Next, we will learn how to use triggers : SQLite- TRIGGERS EXAMPLE